﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CarPark.Helper;


namespace CarPark.MainModel
{
    public partial class MidCarPark
    {

        #region 车型添加
        /// <summary>
        /// nest class which is the entity of a car model
        /// </summary>
        public class CarModel
        {
            public int CarModelNumber = -1;
            public string CarModelName;
            public double CarPrice;
            public CarModel(string s1 , double s2)
            {
                CarModelName = s1;
                CarPrice = s2;
            }
            public CarModel(int n1,string s1 , double s2)
            {
                CarModelNumber = n1;
                CarModelName = s1;
                CarPrice = s2;
            }
            public override string  ToString()
            {
                 return CarModelName;
            }
        }

        /// <summary>
        /// 新增车型和车价，参数为车型名称和单价的实体
        /// 成功返回CK_OK，否则返回错误信息
        /// </summary>
        /// <param name="carModelName"></param>
        /// <param name="carPrice"></param>
        /// <returns></returns>
        public string AddCarModel(CarModel cm)
        {
            int carModelNumber;
            string carModelName = cm.CarModelName;
            double carPrice = cm.CarPrice;
            //查找最大车型号，以确定车型号
            string sql = "SELECT MAX(F_CarModel) FROM T_Price";
            DataTable dt = SQLHelper.GetDataTable(sql, null, null);
            if (dt.Rows.Count > 0)
                carModelNumber = Convert.ToInt16(dt.Rows[0][0]) + 1;
            else
                carModelNumber = 1000;

            sql = "INSERT INTO T_Price (F_CarModel, F_Memo,F_CostPerH) VALUES (";
            sql += "'" + carModelNumber + "',";
            sql += "'" + carModelName + "',";
            sql += "'" + carPrice + "'";
            sql += ")";
            if (1 == SQLHelper.DoExecute(sql, null, null))
                return GlobalConst.CK_OK;
            else
                return "operation failed!";
        }

        /// <summary>
        /// 返回所有车型的名称和单价实体，如果出错则返回null
        /// </summary>
        /// <returns></returns>
        public List<CarModel> GetAllCarModel()
        {
            try
            {
                List<CarModel> ret = new List<CarModel>();
                string sql = "SELECT * FROM T_Price";
                DataTable dt = SQLHelper.GetDataTable(sql, null, null);
                foreach (DataRow dr in dt.Rows)
                {
                    ret.Add(new CarModel(Convert.ToInt32(dr["F_CarModel"]),dr["F_Memo"].ToString(), Convert.ToDouble(dr["F_CostPerH"])));
                }
                return ret;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 删除车型，根据车型号
        /// </summary>
        /// <param name="cm"></param>
        /// <returns></returns>
        public string DeleteCarModel(CarModel cm)
        {
            int carModelNumber = cm.CarModelNumber;

            string sql = "DELETE FROM T_Price WHERE F_CarModel = ";
            sql +=  carModelNumber;
            if (1 == SQLHelper.DoExecute(sql, null, null))
                return GlobalConst.CK_OK;
            else
                return "operation failed!";
        }

        /// <summary>
        /// 修改车型的单价，根据车型号
        /// </summary>
        /// <param name="cm"></param>
        /// <param name="price"></param>
        /// <returns></returns>
        public string UpdateCarPrice(CarModel cm, double price)
        {
            int carModelNumber = cm.CarModelNumber;

            string sql = string.Format("UPDATE T_Price SET F_CostPerH = {0} WHERE F_CarModel = {1}", price, carModelNumber);
            if (1 == SQLHelper.DoExecute(sql, null, null))
                return GlobalConst.CK_OK;
            else
                return "operation failed!";
        }

        /// <summary>
        /// 根据车型名称memo，返回车型id，-1失败
        /// </summary>
        /// <param name="memo"></param>
        /// <returns></returns>
        private int GetModelIDByMemo(string memo)
        {
            string sql = string.Format("select F_CarModel from T_Price where F_Memo = '{0}'",memo);
            DataTable dt = SQLHelper.GetDataTable(sql, null, null);
            if (dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0][0]);
            }
            else
            {
                return -1;
            }
        }


        #endregion


        #region 充值

        /// <summary>
        /// 根据用户卡号或者车牌，充值，卡号和车牌至少有一个不为空
        /// 返回当前用户的具体信息的，返回一个字符串，以在提示框中显示
        /// </summary>
        public string GetCurrentUserInfoByString(ref string cardId, string carNumber)
        {
            string ret = string.Empty;
            string sql;
            string c1 = string.Format("F_CardID = {0}", cardId);
            string c2 = string.Format("F_CarTag = '{0}'", carNumber);
            sql = "select * from TCar where (1>0) ";
            sql += ((cardId == string.Empty) ? string.Empty : (" and " + c1)) + ((carNumber == string.Empty) ? string.Empty : (" and " + c2));
            sql += "and (1>0)";

            DataTable dt = SQLHelper.GetDataTable(sql, null, null);
            if(dt.Rows.Count > 0)
            {
                cardId = dt.Rows[0]["F_CardID"].ToString();
                ret += "卡号：" + dt.Rows[0]["F_CardID"].ToString() + "\n";
                ret += "车牌号：" + dt.Rows[0]["F_CarTag"].ToString() + "\n";
                ret += "车主：" + dt.Rows[0]["F_CarOwner"].ToString() + "\n";
                ret += "车主身份证：" + dt.Rows[0]["F_OwnerId"].ToString() + "\n";
                ret += "车主联系电话：" + dt.Rows[0]["F_OwnerPhone"].ToString() + "\n";
                return ret;
            }
            else
                return GlobalConst.CK_NOUSER;
        }


        /// <summary>
        /// 根据用户卡号或者车牌，充值，卡号和车牌至少有一个不为空，金额是大于0的双精度。
        /// 如果成功，返回GlobalConst.CK_OK，否则返回错误信息
        /// </summary>
        /// <param name="cardId"></param>
        /// <param name="carNumber"></param>
        /// <param name="money"></param>
        public string AddUserMoney(string cardId, string carNumber, double money)
        {
            string sql = string.Format("insert into TCharge (F_CID,F_Quantity,F_Date) values ('{0}','{1}','{2}')",cardId,money,DateTime.Today);
            if (1 == SQLHelper.DoExecute(sql, null, null))
                return GlobalConst.CK_OK;
            else
                return "operation failed!";
        }

        /// <summary>
        ///  获得用户剩余金额
        /// </summary>
        /// <param name="cardId"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetUserBalance(string cardId , out double value)
        {
            double sum = 0;
            try
            {
                string sql = string.Format("select F_Quantity from TCharge where F_CID = {0}", cardId);
                DataTable dt = SQLHelper.GetDataTable(sql, null, null);
                foreach (DataRow dr in dt.Rows)
                    sum += Convert.ToDouble(dr["F_Quantity"]);
                sql = string.Format("select F_Cost from TCost where F_CID = {0}", cardId);
                dt = SQLHelper.GetDataTable(sql, null, null);
                foreach (DataRow dr in dt.Rows)
                    sum -= Convert.ToDouble(dr["F_Cost"]);
                value = sum;
                return GlobalConst.CK_OK;
            }
            catch(Exception e)
            {
                value = sum;
                return e.Message;
            }
        }

        

        #endregion

        #region 用户管理

        /// <summary>
        /// 根据卡号查询用户的所有信息，没有修改columns title
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public DataTable GetUserInfo(string cardId)
        {
            try
            {
                string sql = string.Format("select * from TCar where F_CardID = {0}", cardId);
                DataTable dt = SQLHelper.GetDataTable(sql, null, null);
                return dt;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 根据卡号查询用户充值信息，没有修改columns title
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public DataTable GetUserChargeRecord(string cardId)
        {
            try
            {
                string sql = string.Format("select * from TCharge where F_CID = {0}", cardId);
                DataTable dt = SQLHelper.GetDataTable(sql, null, null);
                return dt;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 根据卡号查询用户停车记录信息，没有修改columns title
        /// </summary>
        /// <param name="cardId"></param>
        /// <returns></returns>
        public DataTable GetUserCostRecord(string cardId)
        {
            try
            {
                string sql = string.Format("select * from TCost where F_CID = {0}", cardId);
                DataTable dt = SQLHelper.GetDataTable(sql, null, null);
                return dt;
            }
            catch
            {
                return null;
            }
        }


        /// <summary>
        /// 添加一个新用户，成功返回CK_OK
        /// </summary>
        /// <param name="cardid"></param>
        /// <param name="carnumber"></param>
        /// <param name="carowner"></param>
        /// <param name="ownerid"></param>
        /// <param name="ownerphone"></param>
        /// <param name="password"></param>
        /// <param name="modelname"></param>
        /// <param name="memo"></param>
        /// <returns></returns>
        public string AddNewUser(   string cardid , 
                                                            string carnumber , 
                                                            string carowner , 
                                                            string ownerid ,
                                                            string ownerphone , 
                                                            string password , 
                                                            string modelname,
                                                            string memo)
        {
            string sql;
            int modelid;
            if(GetUserInfo(cardid).Rows.Count > 0)
            {
                return "invalid card id or car id!";
            }
            modelid = GetModelIDByMemo(modelname);
            if (modelid == -1)
            {
                return "invalid card model!";
            }
            else
            {
                sql = "insert into TCar(F_CardID,F_ModelID,F_CarTag,F_CarOwner,F_OwnerId,F_OwnerPhone,F_Password,F_Status,F_CardStatus,F_Memo) ";
                sql += string.Format("values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}')", cardid,
                                                                                                                                                                    modelid,
                                                                                                                                                                     carnumber,
                                                                                                                                                                     carowner,
                                                                                                                                                                     ownerid,
                                                                                                                                                                     ownerphone,
                                                                                                                                                                     password,
                                                                                                                                                                     false,
                                                                                                                                                                     10,
                                                                                                                                                                     memo
                                                                                                                                                                     );
                if (1 == SQLHelper.DoExecute(sql, null, null))
                    return GlobalConst.CK_OK;
                else
                    return "operation failed!";
            }
        }

        /// <summary>
        /// 获得卡号和车牌号的映射关系，至少要有个值为明确值，未知值传入0或""
        /// </summary>
        /// <param name="CardID"></param>
        /// <param name="CarTag"></param>
        /// <returns></returns>
        public string GetCardIDorCarTag(ref long CardID , ref string CarTag)
        {
            string sql = "";
            if (CardID == 0)
                sql = string.Format("select F_CardID,F_CarTag from TCar where F_CarTag = '{0}'", CarTag);
            else
                sql = string.Format("select F_CardID,F_CarTag from TCar where F_CardID = {0}", CardID);
            try
            {
                DataTable dt = SQLHelper.GetDataTable(sql, null, null);
                if (dt.Rows.Count > 0)
                {
                    CardID = Convert.ToInt32(dt.Rows[0]["F_CardID"]);
                    CarTag = dt.Rows[0]["F_CarTag"].ToString();
                    return GlobalConst.CK_OK;
                }
                else
                    return "No record";
            }
            catch(Exception e)
            {
                return e.Message;
            }
        }

        /// <summary>
        /// 查询车库信息，一定要有ParkID
        /// </summary>
        /// <param name="ParkID"></param>
        /// <param name="CardID"></param>
        /// <param name="bTime"></param>
        /// <param name="eTime"></param>
        /// <param name="?"></param>
        /// <returns></returns>
        public DataTable QueryParkInfo(int ParkID,
                                                                    int CardID,
                                                                    DateAndTimePicker d1,
                                                                    DateAndTimePicker d2
                                                                    )
        {
            DataTable dt = new DataTable();
            string sql;
            sql = "select F_ParkID,F_CID,F_BeginTime,F_EndTime,F_Cost from TCost where F_ParkID = " + ParkID.ToString();
            if (CardID != 0)
                sql += string.Format(" and F_CID={0}", CardID);
            if (d1.IsEnable)
                sql += string.Format(" and F_BeginTime >= {0}", d1.DateAndTime.ToShortDateString());
            if (d2.IsEnable)
                sql += string.Format(" and F_EndTime <= {0}", d2.DateAndTime.ToShortDateString());
            try
            {
                dt = SQLHelper.GetDataTable(sql, null, null);
            }
            catch{}
            return dt;
        }

        #endregion



    }
}
